package database;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

/**
 * Implements the SQLServer interface for a MySQL server.
 */
public class MySQLServer implements SQLServer {
	private String user;
	private String password;
	private String host;
	private Connection connection;
	private Statement statement;
	private ResultSet rs;
	
	public MySQLServer(String host, String user, String password) {
		this.user = user;
		this.password = password;
		this.host = host;
		
		try {
			Class.forName("com.mysql.jdbc.Driver").newInstance();
		} catch (Exception e) {
			throw new RuntimeException("Fout bij het zoeken/laden mysql driver. Is deze wel aanwezig?");
		}
	}
	
	/**
	 * Get a connection to the database.
	 * @return connection
	 * @throws SQLException
	 */
	private Connection getConnection() throws SQLException {
		return DriverManager.getConnection("jdbc:mysql://" + host + "/cco", user, password);
	}  
	
	/**
	 * This function executes a SQL query without an return value. for example an Insert, Update or delete query
	 * @param query a SQL query represented as a String.
	 * @throws SQLException
	 */
  	public void update(String query) throws SQLException {
		try {
			Connection connection = getConnection();
			Statement statement = connection.createStatement();						
			statement.execute(query);
			statement.close();	
			connection.close();	 
		} catch (SQLException e) {
		    displaySQLErrors(e);
		    throw e;
		}
  	}
  	
	/**
	 * This function executes a SQL query with return values. for example an Select query.<br>
	 * <b>Warning after reading the resultset the database connection needs to be closed by calling disConnect().</b>
	 * @param query a SQL query represented as a String.
	 * @throws SQLException
	 * @return ResultSet with the result of the given query
	 */
  	public ResultSet select(String query) throws SQLException {
  		try {  		
  			connection = getConnection(); 
			statement = connection.createStatement();
			rs = statement.executeQuery(query); 
			return rs;
  		} catch (SQLException e) {
		    displaySQLErrors(e);
		    throw e;
		}  	
  	}
  	
  	/**
  	 * Disconnect the connection
  	 * @return when <code>true</code> the connection is successfully disconnected
  	 * @throws SQLException
  	 */  	
  	public boolean disconnect() {
  		try {	
	  		if (rs != null) {
	  			rs.close();
	  			rs = null;
	  		}
	  		if (statement != null) {
	  			statement.close();	
	  		}
	  		if (connection != null) {
	  			connection.close();
	  		}
			return true;
  		} catch (SQLException e) {
		    displaySQLErrors(e);
		    return false;
		}  	
	}
	
	/**
	 * This function tests whether the MySQLServer connection works.
	 * @return when <code>true</code> the connection works.
	 */
	public boolean testConnection() {
		ResultSet res;
		try {
			res = select("SELECT 'true'");	
			while(res.next()) {
				if(res.getString("true").equals("true")) {
					disconnect();
					return true;
				}			
			}
			return false;
		} catch (SQLException e) {
			return false;
		} catch (NullPointerException e) {
			return false;
		}		
	}	
	
	/**
	 * makes a system output from an SQLException
	 * @param e SQLException
	 */
	private static void displaySQLErrors(SQLException e) {
		System.out.println("SQLException: " + e.getMessage());
		System.out.println("SQLState:     " + e.getSQLState());
		System.out.println("VendorError:  " + e.getErrorCode());
	}
	
	public String getUser() {
		return user;
	}

	public String getPassword() {
		return password;
	}
	
	public String getHost() {
		return host;
	}

}
